package com.crm.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.crm.beans.EnqSummary;
import com.crm.entity.Business;
import com.crm.entity.Clientdet;
import com.crm.entity.Opportunity;
import com.crm.entity.Parts;
import com.crm.entity.Quotation;
import com.crm.entity.Sources;
import com.crm.util.ApplicationContext;
import com.crm.util.Constants;


public class OpportunityDAO {
	static Transaction tx;
	protected final Log logger = LogFactory.getLog(getClass());
	
	// Implemented Singleton Pattern Here 
	//start of singleton
	private static OpportunityDAO singleInstance;
	private OpportunityDAO() {}
	
	public static OpportunityDAO getSingleInstance() {
	    if (singleInstance == null) {
	      synchronized (OpportunityDAO.class) {
	        if (singleInstance == null) {
	          singleInstance = new OpportunityDAO();
	          System.out.println("Opportunity Object Created At :"+System.currentTimeMillis());
	        }
	      }
	    }
	    return singleInstance;
	  }
	//end of singleton pattern
	
	
	
	public  int insert(Opportunity c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		    tx = session.beginTransaction();
		     session.saveOrUpdate(c);
		     tx.commit();
		     return 0;
		}
		catch (Exception e) {
		   if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  int update(Opportunity c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		  tx = session.beginTransaction();
		     session.saveOrUpdate(c);
		     tx.commit();
		     return 0;
		}
		catch (Exception e) {
		   if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  int delete(Opportunity c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		   tx = session.beginTransaction();
		     session.delete(c);
		     tx.commit();
		     return 0;
		}
		catch (Exception e) {
		   if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Opportunity> findAll(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		System.out.println("Session is conected "+ session.isConnected());
		try {
		     List<Opportunity> custObjs=new ArrayList<Opportunity>();
		     List objs=null;
		     Query query=session.createQuery("from Opportunity where business_id=:busid");
		     query.setInteger("busid", busid);
		     objs=query.list();
		     if (objs!=null) {
		    	 for (Object o : objs) {
		    		 custObjs.add((Opportunity) o);
		    	 }
		     }
		   return custObjs;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	public  Opportunity findById(int i,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Opportunity o where o.id=:id and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setInteger("id",i);
		     query.setInteger("busid",busid);		
		     Object queryresult=query.uniqueResult();
		     Opportunity opp=(Opportunity)queryresult;
		     return opp;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Opportunity getOpp(String enqnum,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		System.out.println("Session is conected "+ session.isConnected());
		try {
		     String querystr="From Opportunity o where o.enqnum=:eqnum and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setString("eqnum", enqnum);
		     query.setInteger("busid", busid);
		     Object queryresult=query.uniqueResult();
		     Opportunity opp=(Opportunity)queryresult;
		     return opp;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
		
	}
	public  List<Object> getcli(int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		System.out.println("Session is conected "+ session.isConnected());
		try {
		     String querystr="From Clientdet where is_deleted!=1 and busid=:busid";
		     Query query=session.createQuery(querystr);
		     query.setInteger("busid", busid);
		     logger.info("OPPORTUNITY DAO");
		     List<Object> names= query.list();	
		     return names;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Long maxid(int busid,String enqtype) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr = null;
		     Calendar currentDate = Calendar.getInstance();
		     SimpleDateFormat formatter= 
		    	 new SimpleDateFormat("yyyy-MM-dd");
		     String dt = formatter.format(currentDate.getTime());
		     String[] temp=dt.split("-");
		     String year=temp[0];
		     String month=temp[1];
		     String date=temp[2];
		     String dat=null;
		      if(enqtype.equals("BQ")){
		    	 dat=year;
		    	 if(date.equals("01")&&month.equals("04")){
		    		 dat=date+"-"+month+"-"+year;
		    		 querystr="select COUNT(*)+1 from Opportunity where business_id=:busid and ref_num like '%BQ%01-04-"+year+"%'";
		    	 }
		    	 else
		    	 {
		    		 querystr="select COUNT(*)+1 from Opportunity where business_id=:busid and ref_num like '%BQ%"+year+"%'";
		    	 }
		     }else if(enqtype.equals("CP")||enqtype.equals("TE") || enqtype.equals("OPTS")){
		    	 dat=month+"-"+year;
		    	 querystr="select COUNT(*)+1 from Opportunity where business_id=:busid and ref_num like '%"+enqtype+"%"+dat+"%'";
			
		     }else{
		    	 logger.info(" There is a problem with enquiry type");
		     }
		     
		
		     Query query=session.createQuery(querystr);
		     query.setInteger("busid",busid);
		     Object queryresult=query.uniqueResult();
		     Long opp=(Long) queryresult;
		     return opp;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  String getSource(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		System.out.println("Session is conected "+ session.isConnected());
		try {
		     String querystr="From Sourcedet where is_deleted!=1 and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setInteger("busid", busid);
			List<Object> queryresult=query.list();
			String emails="";
			for(Object o:queryresult){
				Sources sd=(Sources)o;
				emails=emails+sd.getEmail()+";";
			}
			return emails;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}

	public  Clientdet Divisions(String org,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="from Clientdet where organisation=:organisation and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setInteger("busid", busid);
		     logger.info(query);
		     String organisation=org;
		     query.setString("organisation", organisation);
		     Object queryresult=query.uniqueResult();
		     Clientdet o= (Clientdet) queryresult;
		     logger.info(o);
		     return o;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	public  Opportunity getLstEnq(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String qstr="From Opportunity where business_id=:busid";
		     Query qry=session.createQuery(qstr);
		     qry.setInteger("busid", busid);
		     List<Object> enqs=qry.list();
		     Opportunity o=new Opportunity();
		     for(int i=0;i<enqs.size();i++){
		    	 o=(Opportunity)enqs.get(i);
		     }
		     return o;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
		
	public  List<Opportunity> getAllEnqSources(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		System.out.println("Session is conected "+ session.isConnected());
		try {
		     List<Opportunity> custObjs=new ArrayList<Opportunity>();
		     List objs=null;
		     String qstr="From Opportunity where business_id=:busid and is_src_completed=false and (status='"+Constants.ST_SOURCE_INFO_SENT+"' or status='"+Constants.ST_SOURCE_INFO_PAR_IMPORT+"' ) and ref_num is not null";
		     Query qry=session.createQuery(qstr);
		     qry.setInteger("busid", busid);
		     objs=qry.list();
		     for(Object o:objs){
		    	 custObjs.add((Opportunity)o);
		     }
		     return custObjs ;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Opportunity> getAllEnqCompares(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		System.out.println("Session is conected "+ session.isConnected());
		try {
		     List<Opportunity> custObjs=new ArrayList<Opportunity>();
		     List objs=null;
		     String qstr="From Opportunity where business_id=:busid and (status='"+Constants.ST_SOURCE_INFO_RECEIVED+"' or status='"+Constants.ST_SOURCE_INFO_PAR_IMPORT+"') and ref_num is not null";
		     Query qry=session.createQuery(qstr);
		     qry.setInteger("busid", busid);
		     objs=qry.list();
		     for(Object o:objs){
		    	 custObjs.add((Opportunity)o);
		     }
		   return custObjs ;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Opportunity> getAllEnqSuppliers(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		System.out.println("Session is conected "+ session.isConnected());
		try {
		     List<Opportunity> custObjs=new ArrayList<Opportunity>();
		     List objs=null;
		     String qstr="From Opportunity where business_id=:busid and (status='"+Constants.ST_GENERATED_QUOTATION+"' ) and ref_num is not null";
		     Query qry=session.createQuery(qstr);
		     qry.setInteger("busid", busid);
		     objs=qry.list();
		     for(Object o:objs){
		    	 custObjs.add((Opportunity)o);
		     }
		     return custObjs ;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Opportunity> getallenqs(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		System.out.println("Session is conected "+ session.isConnected());
		try {
		     List<Opportunity> custObjs=new ArrayList<Opportunity>();
		     List objs=null;
		     String qstr="From Opportunity where ref_num is not null and business_id=:busid";
		     Query qry=session.createQuery(qstr);
		     qry.setInteger("busid", busid);
		     objs=qry.list();
		     for(Object o:objs){
		    	 custObjs.add((Opportunity)o);
		     }
		     return custObjs ;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	
	
	public  List<Opportunity> getenqs(String month,String year,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Opportunity  where curr_date like '"+year+"-"+month+"%' and business_id=:busid";
		     logger.info(querystr);
		     Query query=session.createQuery(querystr);
		     query.setInteger("busid", busid);
		     Object queryresult=query.list();
		     List<Opportunity> enqs=(List<Opportunity>) queryresult;
		     return enqs;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Opportunity getByRefNo(int busid,String refNo) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Opportunity where business_id=:busid and ref_num=:refNo ";
		     Query query=session.createQuery(querystr);
		     query.setString("refNo", refNo);
		     query.setInteger("busid", busid);
		     Object queryresult=query.uniqueResult();
		     Opportunity comp=(Opportunity)queryresult;
		     return comp;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  int getenqid(String enqnum,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Opportunity where enqnum=:enqnum and business_id=:busid";
		     logger.info(querystr);
		     Query query=session.createQuery(querystr);
		     query.setInteger("busid", busid);
		     query.setString("enqnum", enqnum);
		     Object queryresult=query.uniqueResult();
		     Opportunity o=(Opportunity)queryresult;
		     int id=o.getId();
		     return id;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	public  Opportunity getOpportunityByRefNo(String refnum,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="from Opportunity where ref_num=:ref and business_id=:busid";
		     Query qury=session.createQuery(querystr);
		     qury.setInteger("busid", busid);
		     qury.setString("ref", refnum);
		     Object queryresult=qury.uniqueResult();
		     return (queryresult !=null)?(Opportunity)queryresult:null;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}	
	
	public  void updateStatus(int busid,String status, int rid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		    tx = session.beginTransaction();
		     String querystr="update Opportunity set status=:st where id=:rid and business_id=:busid";
		     Query qury=session.createQuery(querystr);
		     qury.setInteger("rid", rid);
		     qury.setInteger("busid", busid);
		     qury.setString("st", status);
		     qury.executeUpdate();
		     tx.commit();
		     return ;
		}
		catch (Exception e) {
		     if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  void removeRef(int rid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
		     String querystr="update Opportunity set is_src_completed=true where id=:rid and business_id=:busid";
		     Query qury=session.createQuery(querystr);
		     qury.setInteger("rid", rid);
		     qury.setInteger("busid", busid);
		     qury.executeUpdate();
		     tx.commit();
		     return ;
		}
		catch (Exception e) {
		    if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  void isSourceCompleted(int rid,boolean status,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		    tx = session.beginTransaction();
		     String querystr="update Opportunity set is_src_completed=:status where id=:rid and business_id=:busid";
		     Query qury=session.createQuery(querystr);
		     qury.setInteger("rid", rid);
		     qury.setInteger("busid", busid);
		     qury.setBoolean("status", status);
		     qury.executeUpdate();
		     tx.commit();
		     return   ;
		}
		catch (Exception e) {
		     if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  void updateRemarks(String refno,String rem,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
		     String querystr="update Opportunity set remarks=:rem where ref_num=:refno and business_id=:busid";
		     Query qury=session.createQuery(querystr);
		     qury.setString("refno", refno);
		     qury.setInteger("busid", busid);
		     qury.setString("rem", rem);
		     qury.executeUpdate();
		     tx.commit();
		     return ;
		}
		catch (Exception e) {
		     if (tx!=null) tx.rollback();
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Opportunity getOppById(int oppid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Opportunity where id=:oppid and business_id=:busid";
		     Query qury=session.createQuery(querystr);
		     qury.setInteger("oppid", oppid);
		     qury.setInteger("busid", busid);
		     Object names= qury.uniqueResult();
		     Opportunity op=(Opportunity)names;
		     return op;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
		}
	public  Opportunity getByRef(String ref,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Opportunity where ref_num=:ref and business_id=:busid";
		     Query qury=session.createQuery(querystr);
		     qury.setString("ref",ref);
		     qury.setInteger("busid", busid);
		     Object n= qury.uniqueResult();
		     Opportunity op=(Opportunity)n;
		     return op;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Object> geteqs(String year,int busid) throws Exception  {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Opportunity  where curr_date like '"+year+"%' and business_id=:busid";
		     logger.info(querystr);
		     Query query=session.createQuery(querystr);
		     Object queryresult=query.list();
		     List<Object> enqs=(List<Object>) queryresult;
		     return enqs;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Opportunity> getByStatus(String status,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		System.out.println("Session is conected "+ session.isConnected());
		try {
		     String querystr="From Opportunity  where status=:status and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setString("status", status);
		     query.setInteger("busid", busid);
		     List<Opportunity> lo=(List<Opportunity>)query.list();
		     return lo;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  Set<String> getExecutive(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     Set<String> exName=new HashSet<String>();
		     String querystr=" from Opportunity where busid=:busid";
		     Query query=session.createQuery(querystr);
		     query.setInteger("busid", busid);
		     List<Opportunity> lo=(List<Opportunity>)query.list();
		     for(Opportunity op:lo){
		    	 if(op!=null){
		    		 String st=op.getExecName();
		    		 exName.add(st);
		    	 }
		     }
		     return exName;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	public  List<Opportunity> getRef(String exName, int currentDate,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Opportunity  where exec_name=:name and curr_date like '"+currentDate+"%' and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setString("name", exName);
		     query.setInteger("busid", busid);
		     List<Opportunity> lo=(List<Opportunity>)query.list();
		     return lo;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Opportunity> getRef(String exName,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Opportunity  where exec_name=:name and business_id=:busid";
			Query query=session.createQuery(querystr);
			query.setString("name", exName);
			query.setInteger("busid", busid);
			List<Opportunity> lo=(List<Opportunity>)query.list();
			return lo;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<Object> getRefId(String exName, int mn, int yy,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="";
		     if(mn==10||mn==11||mn==12){
		    	 querystr="From Opportunity  where exec_name=:nam and curr_date like '"+yy+"-"+mn+"%' and business_id=:busid";
		     }else{
		    	 querystr="From Opportunity  where exec_name=:nam and curr_date like '"+yy+"-0"+mn+"%' and business_id=:busid";
		     }
		     Query query=session.createQuery(querystr);
		     query.setString("nam", exName);
		     query.setInteger("busid", busid);
		     List<Object> lo=(List<Object>)query.list();
		     return lo;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Object> getByFDayTDay(String exName, int mn, int yy,
			int fd, int td,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="";
		     if(mn==10||mn==11||mn==12){
		    	 querystr="From Opportunity  where exec_name=:nam and curr_date between '"+yy+"-"+mn+"-"+fd+"' and '"+yy+"-"+mn+"-"+td+"' and business_id=:busid";
		
		     }else{
		    	 querystr="From Opportunity  where exec_name=:nam and curr_date between '"+yy+"-0"+mn+"-"+fd+"' and '"+yy+"-0"+mn+"-"+td+"' and business_id=:busid";
			
		     }
		     Query query=session.createQuery(querystr);
		     query.setString("nam", exName);
		     query.setInteger("busid", busid);
		     List<Object> lo=(List<Object>)query.list();
		     return lo;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<EnqSummary> getEnquirySumm(String part,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     List<EnqSummary> ls=new ArrayList<EnqSummary>();
		     if(part.trim().equals(""))
		     {
		    	 part="_";
		     }
		     String str="from Opportunity where id in(select enqid from Parts where prtnum like '%"+part+"%') and business_id=:busid";
		     System.out.println("the query is "+str);
		     Query q=session.createQuery(str);
		     q.setInteger("busid", busid);
		     List<Object> ps=(List<Object>)q.list();
		     Iterator itr=ps.iterator();
		     int i=1;
		     while(itr.hasNext()){
		    	 Object obj=itr.next();
		    	 Opportunity op=(Opportunity)obj;
		    	 if(op!=null){
		    		 EnqSummary es=new EnqSummary();
		    		 es.sno=i++; 
		    		 es.refNo=op.getRefNum();
			
		    		 es.isEnq=Boolean.TRUE;
		    		 String s=op.getStatus();
		    		 if(s!=""){
		    			 Integer p=Constants.getStatus(s);
		    			 if(p!=null && p!=0){
		    				 if(p>0)es.isEnq=Boolean.TRUE;
		    				 if(p>4)es.isQuo=Boolean.TRUE;
		    				 if(p>5)es.isPO=Boolean.TRUE;
		    				 if(p>6)es.isInvoice=Boolean.TRUE;
		    			 }
		    		 }
		    		 es.id=op.getId();
		    		 ls.add(es);
		    	 }
		     }
		     return ls;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	public List<Opportunity> getStatus(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Opportunity  where business_id=:id order by id desc";
		     Query query=session.createQuery(querystr);
		     query.setInteger("id",busid);
		     List<Opportunity> lo=(List<Opportunity>)query.list();
		    
		     return lo;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Opportunity> getEnqByStatus(int busid,String status) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     List<Opportunity> custObjs=new ArrayList<Opportunity>();
		     String qstr="From Opportunity where business_id=:busid and (status='"+Constants.ST_GENERATED_QUOTATION+"'  or status=:status ) and ref_num is not null";
		     Query qry=session.createQuery(qstr);
		     qry.setString("status",status);
		     qry.setInteger("busid",busid);
			custObjs=(List<Opportunity>)qry.list();
			return custObjs ;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	public  List<Opportunity> getEnqByStatusForPrint(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     List<Opportunity> custObjs=new ArrayList<Opportunity>();
		     String qstr="From Opportunity where business_id=:busid or (status='"+Constants.ST_GENERATED_QUOTATION+"' )  or (status='"+Constants.ST_GENERATED_INVOICE+"' )  or (status='"+Constants.ST_GENERATED_SO+"' )  or (status='"+Constants.ST_GEN_PO+"' ) and ref_num is not null";
		     Query qry=session.createQuery(qstr);
		     qry.setInteger("busid",busid);
		     custObjs=(List<Opportunity>)qry.list();
		     return custObjs ;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
		}
private  Session startOperations()throws Exception {
		
		Session session = ApplicationContext.getHiberSession().openSession();
		tx=session.beginTransaction();
		return session;
		
	}
	public List<Opportunity> getEnquiry(int businessId, String exeName) throws Exception {
		List<Opportunity> ob=new ArrayList<Opportunity>();
		Session session = startOperations();
		try {
		String qrystr="From Opportunity where business_id=:id and exec_name=:name ";
		
		Query qr=session.createQuery(qrystr);
		qr.setInteger("id", businessId);
		qr.setString("name", exeName);
		ob=qr.list();
		
		return ob;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
			session=null;
		}
	}
	public List<Opportunity> findByPart(String partname, int busid) throws Exception {
		List<Opportunity> ob=new ArrayList<Opportunity>();
		Session session = startOperations();
		try {
		String qrystr="from Opportunity where id in(select enqid from Parts where prtnum like '%"+partname+"%') and business_id=:busid";
		
		Query qr=session.createQuery(qrystr);
		qr.setInteger("busid", busid);
		ob=qr.list();
		
		return ob;
		}catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
			session=null;
		}
	}
	
	
/*	public  List<Object> getRefNum(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="from Opportunity  where business_id=:busid and is_src_completed=false ORDER BY id DESC";
			Query query=session.createQuery(querystr);
			query.setInteger("busid", busid);
			List<Object> qresult=query.list();
			return qresult;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}*/

	}
